from datetime import datetime

import pymysql

host = "localhost"
port = 3306
user = "root"
passwd = "root"
db = "db_uestc"


def db_login(host, port, user, passwd, database):
    conn = pymysql.connect(host=host, port=port, user=user, passwd=passwd, db=database,
                           charset="utf8")
    return conn


def get_users():
    global host, port, user, passwd, db

    conn = db_login(host, port, user, passwd, db)
    cur = conn.cursor()
    sql = '''
        select id, username
        from db_uestc.users
        where permit = 1;
    '''
    permit_user = []
    try:
        cur.execute(sql)
        results = cur.fetchall()
        for row in results:
            id = row[0]
            username = row[1]
            permit_user.append([id, username])
    except Exception as e:
        print(e)
    finally:
        conn.close()
        return permit_user


def get_daily_report_info():
    global host, port, user, passwd, db

    conn = db_login(host, port, user, passwd, db)
    cur = conn.cursor()
    sql = '''
        select user_id, username, stu_number, pwd, email, dr.send_date as 'send_date' from db_uestc.users
        left join db_uestc.daily_report dr on db_uestc.users.id = dr.user_id
        where db_uestc.users.permit = 1 and dr.permit = 1 and dr.send_date <> '%s';
        ''' % datetime.now().strftime("%Y-%m-%d")
    daily_report_users = []
    try:
        cur.execute(sql)
        results = cur.fetchall()
        for row in results:
            user_id = row[0]
            username = row[1]
            stu_number = row[2]
            pwd = row[3]
            email = row[4]
            daily_report_users.append([user_id, username, stu_number, pwd, email])
    except Exception as e:
        print(e)
    finally:
        conn.close()
        return daily_report_users


def db_insert(sql):
    global host, port, user, passwd, db
    flag = False

    conn = db_login(host, port, user, passwd, db)
    cursor = conn.cursor()

    try:
        cursor.execute(sql)
        conn.commit()
        flag = True
    except Exception as e:
        print(e)
    finally:
        conn.close()
        return flag


def db_delete(sql):
    global host, port, user, passwd, db
    flag = False

    conn = db_login(host, port, user, passwd, db)
    cursor = conn.cursor()

    try:
        cursor.execute(sql)
        conn.commit()
        flag = True
    except Exception as e:
        print(e)
    finally:
        conn.close()
        return flag


def db_update(sql):
    global host, port, user, passwd, db
    flag = False

    conn = db_login(host, port, user, passwd, db)
    cursor = conn.cursor()
    try:
        cursor.execute(sql)
        conn.commit()
        flag = True
    except Exception as e:
        print(e)
    finally:
        conn.close()
        return flag


if __name__ == '__main__':
    # just for tests
    db_update('''
    update db_uestc.daily_report
    set send_date = '%s'
    where user_id = 1;
    ''' % datetime.now().strftime("%Y-%m-%d"))
    print(get_daily_report_info())
